<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class Data_Model extends Model {

    public function __construct() {
        parent::__construct();
//        echo 'user disini';
    }

    public function dataList() {
        $nrp = SESSION::get('nrp');

        $sth = $this->db->prepare("SELECT data.id, data.kd_makul, nilai_reff.nilai, makul.nama           
                                    FROM 
                                    (data LEFT JOIN nilai_reff ON data.nilai = nilai_reff.kd_nilai)
                                    LEFT JOIN
                                    makul 
                                    ON                          
                                    data.kd_makul = makul.kd_makul
                                    WHERE
                                    nrp = :nrp                                   
                                    ");


        $sth->execute(array(
            ':nrp' => $nrp
        ));

        $data = $sth->fetchAll();

        return $data;

//        print_r($data);
    }

    public function addData($data) {
//        echo 2222;
        echo $data;
//        $username = $_POST['username'];
//        $password = MD5($_POST['password']);
//        $role = $_POST['role'];

        $sth = $this->db->prepare
                (
                "INSERT INTO data 
                (makul,nilai)
                VALUES 
                (:makul,:nilai)"
        );
        $sth->execute(array(
            ':makul' => $data['makul'],
            ':nilai' => $data['nilai']
        ));
    }

    public function deleteData($id) {

        $sth = $this->db->prepare("DELETE from data where id = :id");

        $sth->execute(array(
            ':id' => $id
        ));
    }

//    public function editData($id) {
//        $sth = $this->db->prepare("select * from users where user_id = :id");
//
//        $sth->execute(array(
//            ':id' => $id
//        ));
//
//        $data = $sth->fetch();
//
//        return $data;
//    }

    public function save($file_name, $source) {

        move_uploaded_file($source, "files/" . $file_name);
    }

    public function readFile($file_name) {
        $file = $file_name;

        $data = new Spreadsheet_Excel_Reader($file);

        return $data;
    }

    public function read_ext() {
        $file = false;

        $ext = explode('.', $_FILES['upload']['name']);
        $fileext = $ext[count($ext) - 1];


        if ($fileext == "xls") {
            $file = TRUE;
        }
        return $file;
    }

    public function readExcel() {
        require 'public/PHPExcel/Classes/PHPExcel.php';
        require_once 'public/PHPExcel/Classes/PHPExcel/IOFactory.php';

        $tmp_file = $_FILES['upload']['tmp_name'];


        $ObjExcel = PHPExcel_IOFactory::load($tmp_file);

//        return $ObjExcel;

        foreach ($ObjExcel->getWorksheetIterator() as $worksheet) {

//            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            //untun mendapatkan jumlah kolom yang ada
            $columncount = ord($highestColumn) - 64;
        }
//        var_dump($highestRow);
//        var_dump($highestColumn);
//        var_dump($columncount);


        $data = array();
        for ($row = 2; $row < $highestRow; $row++) {

            $temp = array();

            for ($column = 0; $column < $columncount; $column++) {

                $cell = $worksheet->getCellByColumnAndRow($column, $row);
                $value = $cell->getValue();

                array_push($temp, $value);
            }
            array_push($data, $temp);
        }

        session::set('data', $data);
        return $data;

//        print_r($data);
    }

    public function insertRecord($data) {

        $value = explode(',', $data);

        $sth = $this->db->prepare("INSERT INTO data (nrp,kd_makul,nilai) VALUES (:user,:makul,:nilai)");

        $sth->execute(array(
            ':user' => session::get('nrp'),
            ':makul' => $value[0],
            ':nilai' => $value[1]
        ));


//        print_r($value[1])   
//        print_r($user);
        header('location:' . URL . 'data/read');
    }

    public function simpan() {
        $data = Session::get('data');
        $nrp = SESSION::get('nrp');

//        var_dump($data);
        //mencoba mencari record yang dobel
        foreach ($data as $key => $nilai) {

            $query = $this->db->prepare("SELECT id FROM data WHERE 
                                        nrp = :nrp 
                                        and 
                                        kd_makul = :kd_makul ");

            $query->execute(array(
                ':nrp' => $nrp,
                ':kd_makul' => $nilai[0]
            ));

            $result = $query->fetchObject();


            if ($result) {
                foreach ($result as $key => $value) {

                    $delete = $this->db->prepare("DELETE FROM data WHERE id = :id");

                    $delete->execute(array(
                        ':id' => $value
                    ));
                }
            }
        }
        //menyimpan data 

        foreach ($data as $key => $record) {

            switch ($record['4']) {
                case 'A': $data[$key][] = 4.0;
                    break;

                case 'AB': $data[$key][] = 3.5;

                    break;
                case 'B': $data[$key][] = 3.0;


                    break;
                case 'BC': $data[$key][] = 2.5;


                    break;
                case 'C': $data[$key][] = 2.0;


                    break;
                case 'D': $data[$key][] = 1.0;


                    break;
                case 'E': $data[$key][] = 0.0;


                    break;
            }
            
//            print_r($data);
//            echo '<br>';
        }
        foreach ($data as $key => $value) {
            $sth = $this->db->prepare("INSERT INTO data 
                                        (nrp,kd_makul,nilai,x2) 
                                        VALUES 
                                        (:user,:makul,:nilai,:x2)");

            $sth->execute(array(
                ':user' => session::get('nrp'),
                ':makul' => $value[0],
                ':nilai' => $value[5],
                ':x2' => $value[5]*$value[5]
            ));
        }
//        var_dump($data);
//        echo '<br>';
        unset($_SESSION['data']);
        header('location:' . URL . 'data');
    }

    public function page() {

        $nrp = SESSION::get('nrp');

        $item_page = 10;

        $sth = $this->db->prepare("select makul.nama, data.nilai 
                                    from makul 
                                    INNER JOIN
                                    data 
                                    on 
                                    makul.kd_makul = data.kd_makul
                                    where
                                    nrp = :nrp
                                    ");

        $sth->execute(array(
            ':nrp' => $nrp
        ));

        $row = $sth->rowCount();

        $page = ceil($row / $item_page);

        return $page;
    }

    public function edit($id) {

        $sth = $this->db->prepare("SELECT 
            *
            FROM
            data
            WHERE
            id=:id");

        $sth->execute(array(
            ':id' => $id
        ));

        $result = $sth->fetch();
//        print_r($result);
        return $result;
    }

    public function edit_save($data) {

        $sth = $this->db->prepare("UPDATE data 
            SET nilai=:nilai
            WHERE
            kd_makul=:kd_makul");
        $sth->execute(array(
            ':nilai' => $data['nilai'],
            ':kd_makul' => $data['kd_makul']
        ));
    }

    public function list_makul() {
        $nrp = session::get('nrp');
        $sth = $this->db->prepare("SELECT kd_univ,kd_jurusan 
            FROM 
            user_detail 
            WHERE 
            nrp = :nrp ");
        $sth->execute(array(
            ':nrp' => $nrp
        ));

        $data = $sth->fetch();

        $kd_univ = $data['kd_univ'];
        $kd_jurusan = $data['kd_jurusan'];

        $sth2 = $this->db->prepare("select kd_makul,nama from makul 
            where 
            kd_univ = :kd_univ 
            and 
            kd_jurusan = :kd_jurusan");

        $sth2->execute(array(
            ':kd_univ' => $kd_univ,
            ':kd_jurusan' => $kd_jurusan
        ));

        $data2 = $sth2->fetchAll();
        return $data2;
    }

    public function save_data($data) {

        $nrp = Session::get('nrp');

        $counter = count($data['kd_makul']);

//        print_r($counter);


        if ($counter == 1) {
            $sth = $this->db->prepare("insert into data
                (nrp,kd_makul,nilai,x2)
                values
                (:nrp,:kd_makul,:nilai,:x2)"
            );

            $sth->execute(array(
                ':nrp' => $nrp,
                ':kd_makul' => $data['kd_makul']['0'],
                ':nilai' => $data['nilai']['0'],
                ':x2' => ($data['nilai']['0'] * $data['nilai']['0'])
            ));
        } else {

            for ($i = 0; $i < $counter; $i++) {

                $sth = $this->db->prepare("insert into data
                (nrp,kd_makul,nilai,x2)
                values
                (:nrp,:kd_makul,:nilai,:x2)"
                );

                $sth->execute(array(
                    ':nrp' => $nrp,
                    ':kd_makul' => $data['kd_makul'][$i],
                    ':nilai' => $data['nilai'][$i],
                    ':x2' => ($data['nilai'][$i] * $data['nilai'][$i])
                ));
            }
        }
    }

}

?>
